# -*- coding: utf-8 -*-

from openerp import tools
from openerp.osv import fields, osv



class purchase_order_gross_profit(osv.osv):

    # 采购单产品毛利统计
    _name = 'purchase.order.gross.profit'
    _description = u'采购产品毛利统计'
    _auto = False

    _columns = {
        'order_id': fields.many2one('purchase.order', string=u'采购单', readonly=True),
        'product_id': fields.many2one('product.product', u'产品', readonly=True),
        'product_qty': fields.float(string=u'采购数量', readonly=True),
        'out_quant_sum': fields.float(string=u'出库数量', readonly=True),
        'purchase_cost': fields.float(string=u'平均采购成本', readonly=True),
        'purchase_cost_subtotal': fields.float(string=u'采购成本', readonly=True),
        'avg_sale_price': fields.float(string=u'平均销售价', readonly=True),
        'sale_price_subtotal': fields.float(string=u'售价', readonly=True),
        'gross_profit_subtotal': fields.float(string=u'毛利', readonly=True),
        'create_uid': fields.many2one('res.users', string=u'建立人', readonly=True),
        'date_order': fields.date(string=u'采购日期', readonly=True),
    }

    #建立视图
    def init(self, cr):
        tools.drop_view_if_exists(cr, self._table)
        cr.execute("""CREATE OR REPLACE VIEW %s AS (
            SELECT s.*, (purchase_cost * out_quant_sum) as purchase_cost_subtotal, (avg_sale_price * out_quant_sum) as sale_price_subtotal,  (avg_sale_price - purchase_cost) * out_quant_sum AS gross_profit_subtotal, po.create_uid, DATE(po.date_order) date_order from(
SELECT pol.id, pol.order_id, pol.product_id, pol.product_qty, sum(sq.qty) AS out_quant_sum, pol.price_unit AS purchase_cost,
    (SELECT (CASE WHEN sum(sol.product_uom_qty)>0 THEN ROUND(sum(sol.price_unit*sol.product_uom_qty)/sum(sol.product_uom_qty), 2) ELSE 0 END)
        FROM sale_order_line sol
     INNER JOIN sale_order so ON so.id = sol.order_id
     INNER JOIN order_type ot ON so.order_type_id = ot.id
     INNER JOIN stock_move so_sm ON so_sm.so_line_id = sol.id
     INNER JOIN stock_quant_move_rel so_sqmr ON so_sqmr.move_id=so_sm.id
    WHERE so_sqmr.quant_id = any(array_agg(sq.id)) AND ot.order_type_code NOT IN ('card_order', 'card_exchange_order')
    AND so.partner_id != 35636
    ) AS avg_sale_price FROM purchase_order_line pol
 INNER JOIN stock_move sm ON sm.purchase_line_id = pol.id
 INNER JOIN stock_quant_move_rel sqmr ON sqmr.move_id=sm.id
 INNER JOIN stock_quant sq ON sq.id=sqmr.quant_id
 WHERE sq.location_id NOT IN (12, 18, 25, 24) AND pol.product_qty >0
 GROUP BY pol.id
)s
INNER JOIN purchase_order po ON po.id=s.order_id
WHERE s.out_quant_sum<=s.product_qty AND avg_sale_price>0)""" % (self._table,))
